package com.dys.dao.impl;

import com.dys.dao.ClassDao;
import com.dys.entity.Clazz;
import com.dys.util.ConnectionPool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: 疾风
 * @ClassName: ClassDaoImpl
 * @Description: 班级具体实现类
 * @CreateDate: 2024/09/26
 * @Version: v1.0.0
 */
public class ClassDaoImpl implements ClassDao {
    @Override
    public int insertOneClass(Clazz c) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        int result = 0;
        try {
            // 预编译SQL
            ps = conn.prepareStatement("insert into t_class values(default,?,?,?)");
            // 设置预编译参数
            ps.setString(1, c.getCname());
            ps.setString(2, c.getClocation());
            ps.setString(3, c.getDesc());
            // 执行SQL
            result = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, null);
        }
        return result;
    }

    @Override
    public List<Clazz> listClass() {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Clazz> listClasss = new ArrayList<Clazz>();
        try {
            // 预编译SQL
            ps = conn.prepareStatement("select * from t_class");
            // 执行SQL
            rs = ps.executeQuery();
            // 将数据封装为Clazz对象，添加入List集合中：
            while (rs.next()) {
                int cid = rs.getInt("cid");
                String cname = rs.getString("cname");
                String clocation = rs.getString("clocation");
                String desc = rs.getString("desc");
                Clazz clazz = new Clazz(cid, cname, clocation, desc);
                listClasss.add(clazz);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, rs);
        }
        return listClasss;
    }

    @Override
    public List<Clazz> selectAllClasses(int startIndex, int size) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Clazz> list = new ArrayList<Clazz>();
        try {
            // 预编译SQL
            ps = conn.prepareStatement("select * from t_class limit ?,?");
            // 设置参数
            ps.setInt(1, startIndex);
            ps.setInt(2, size);
            // 执行SQL
            rs = ps.executeQuery();
            // 将数据封装为Clazz对象，添加入List集合中：
            while (rs.next()) {
                int cid = rs.getInt("cid");
                String cname = rs.getString("cname");
                String clocation = rs.getString("clocation");
                String desc = rs.getString("desc");
                Clazz clazz = new Clazz(cid, cname, clocation, desc);
                list.add(clazz);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, rs);
        }
        return list;
    }

    @Override
    public int selectCountClass() {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        try {
            // 预编译SQL
            ps = conn.prepareStatement("select count(*) from t_class");
            // 执行SQL
            rs = ps.executeQuery();
            // 获取总记录数
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, rs);
        }
        return count;
    }

    @Override
    public int deleteOneClass(int cid) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        int rowCount = 0;
        try {
            // 预编译SQL
            ps = conn.prepareStatement("delete from t_class where cid=?");
            ps.setInt(1, cid);
            // 执行SQL
            rowCount = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, null);
        }
        return rowCount;
    }

    @Override
    public Clazz selectOneClass(int cid) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Clazz clazz = null;
        try {
            // 预编译SQL
            ps = conn.prepareStatement("select * from t_class where cid=?");
            ps.setInt(1, cid);
            // 执行SQL
            rs = ps.executeQuery();
            // 获取班级信息，封装对象
            if (rs.next()) {
                int cid2 = rs.getInt("cid");
                String cname = rs.getString("cname");
                String clocation = rs.getString("clocation");
                String desc = rs.getString("desc");
                clazz = new Clazz(cid2, cname, clocation, desc);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, rs);
        }
        return clazz;
    }

    @Override
    public int updateOneClass(Clazz c) {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        int rowCount = 0;
        try {
            // 预编译SQL
            ps = conn.prepareStatement("update t_class set cname=?,clocation=?,`desc`=? where cid=?");
            ps.setString(1, c.getCname());
            ps.setString(2, c.getClocation());
            ps.setString(3, c.getDesc());
            ps.setInt(4, c.getCid());
            // 执行SQL
            rowCount = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, null);
        }
        return rowCount;
    }

    @Override
    public List selectClassCount() {
        // 1.获取数据库连接
        Connection conn = ConnectionPool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        // 班级信息
        List listClassInfo = new ArrayList();
        // 班级人数
        List subClassCountList = new ArrayList();
        // 班级名称
        List subClassNames = new ArrayList();
        try {
            // 预编译SQL
            ps = conn.prepareStatement("select count(*),c.cname from t_class c join t_student s on c.cid = s.cid group by c.cname");
            // 执行SQL
            rs = ps.executeQuery();
            // 获取班级信息
            while (rs.next()) {
                int classCount = rs.getInt(1);
                String className = rs.getString(2);
                subClassCountList.add(classCount);
                subClassNames.add(className);

            }
            listClassInfo.add(subClassCountList);
            listClassInfo.add(subClassNames);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接资源
            ConnectionPool.close(conn, ps, rs);
        }
        return listClassInfo;
    }
}
